﻿using System.Collections.Generic;
using System.Data.Objects;

namespace NorthwindEntities
{
    class SelectSalesByRegionInPeriod
    {
        public static IEnumerable<OrderAddressAndName> GetSalesByRegionInPeriond(string region, string startDate,
            string endDate) 
        {
            NorthwindEntities context = new NorthwindEntities();
            string nativeQuerry = "Select ord.ShipAddress," +
	                                "ord.ShipName," +
	                                " ordDetails.Quantity*ordDetails.UnitPrice - ordDetails.Discount as TotalSum " +
                                    " from Orders ord join [Order Details] ordDetails "+
                                    " on ord.OrderID = ordDetails.OrderID " +
                                    " Where ord.ShipRegion = 'RJ' and ord.ShippedDate >= '1997-01-01' " +
                                    " and ord.ShippedDate <= '1998-01-01'";
            object[] parameters = {region,startDate,endDate};
            ObjectResult<OrderAddressAndName> orders = context.ExecuteStoreQuery<OrderAddressAndName>(nativeQuerry,parameters);

            return orders;
        }
    }
}
